/*********************************************************************************
 *      Copyright:  (C) 2022 dairongan<2974932606@qq.com>
 *                  All rights reserved.
 *
 *       Filename:  sql_action.c
 *    Description:  This file sql_action,it is a collection of SQL statement
 *                 
 *        Version:  1.0.0(15/04/22)
 *         Author:  dairongan <2974932606@qq.com>
 *      ChangeLog:  1, Release initial version on "15/04/22 00:15:08"
 *                 
 ********************************************************************************/
#include <sqlite3.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "logger.h"
#include "sqlaction.h"

static sqlite3		 *db;//只用于本C文件


/*--------------------------------------------------------------------------------*/
static inline int callback_select(void *data, int argc, char **argv, char **azColName)
{
	int i;
	info_temp_t	 *info=(info_temp_t *)data;
	if(argv)
	{	
		for(i=1; i<argc; i++)//不复制ID所以从1开始循环
		{
			if(i==1)
			{
				snprintf(info->prod_id,strlen(argv[i])+1,"%s",argv[i]);
			}
			if(i==2)
			{
				snprintf(info->ttime,strlen(argv[i])+1,"%s",argv[i]);
			}
			if(i==3)
			{
				snprintf(info->temp,strlen(argv[i])+1,"%s",argv[i]);
			}
		}
	}
   return 0;
}
/* ---------------------------------创建数据库和创建表------------------------------- */

int sql_create_db_tb(char *dbname)//create a db file
{ 
	char		 *zErrMsg=0;
	char		**dbresult;
	int			row=0;
	int			column=0;
	int			 rv;
	char		 sql[128];
	

	rv = sqlite3_open(dbname, &db);
    if( rv )
	{
    	log_error("Can't open database: %s\n", sqlite3_errmsg(db));
		exit(0);
	}
	else
	{
		log_info("Opened database successfully\n");
	}

	 memset(sql,0,sizeof(sql));
     strncpy(sql,"CREATE TABLE TEMP_INFO("\
			 	 "ID INT PRIMARY KEY NOT NULL," \
                 "PROD_ID CHAR(16) NOT NULL," \
                 "TIME CHAR(16) NOT NULL," \
                 "TEMP CHAR(16) NOT NULL);",sizeof(sql));

	rv = sqlite3_get_table(db, sql, &dbresult,&row,&column, &zErrMsg);
    if( rv != SQLITE_OK )
	{
    	log_error("CREATE SQL error: %s\n", zErrMsg);
		return -1;
    }
	else
	{
    	log_info("create operation done successfully\n");
    }
    return 1;
}

/*-------------------------------获取表中有多少条数据并返回------------------------------------*/
int sql_number_of_row()//find number_of_row
{
	int			 rv;
	char		**dbresult;
	int			row=0;
	int			column=0;
	char		*zErrMsg=0;

	rv = sqlite3_get_table(db, "SELECT TEMP FROM TEMP_INFO;",&dbresult,&row,&column,&zErrMsg);
    if( rv != SQLITE_OK )
	{
    	log_error("SQL_MAXID error: %s\n", zErrMsg);
     	sql_term(db);
		return 0;//return a min_id
    }
	else
    	log_info("sql_number_of_row operation done successfully\n");
    return row;
}
/*-------------------------------获取最大ID并返回------------------------------------*/
static inline int sql_maxid()//find max_id
{
	int			 rv;
	char		**dbresult;
	int			row=0;
	int			column=0;
	int			max_id=0;
	char		*zErrMsg=0;

	rv = sqlite3_get_table(db, "SELECT MAX(ID) FROM TEMP_INFO",&dbresult,&row,&column,&zErrMsg);
    if( rv != SQLITE_OK )
	{
    	log_error("SQL_MAXID error: %s\n", zErrMsg);
     	sql_term(db);
		return 0;//return a min_id
    }
	else
	{	
		max_id=dbresult[1] ? atoi(dbresult[1]):0;
    	log_info("max_id operation done successfully\n");
    }
    return max_id;
}
/*-------------------------------获取最小ID并返回------------------------------------*/
static inline int sql_minid()//find min_id
{
	int			 rv;
	char		**dbresult;
	int			row=0;
	int			column=0;
	int			min_id=0;
	char		*zErrMsg=0;

	rv = sqlite3_get_table(db, "SELECT MIN(ID) FROM TEMP_INFO",&dbresult,&row,&column,&zErrMsg);
    if( rv != SQLITE_OK )
	{
    	log_error("SQL_MAXID error: %s\n", zErrMsg);
     	sql_term(db);
		return 0;//return a min_id
    }
	else
	{	
		min_id=dbresult[1] ? atoi(dbresult[1]):0;
    	log_info("max_id operation done successfully\n");
    }
    return min_id;
}

/* --------------------------------获取第一条数据的值----------------------------------- */

int sql_select_one_data(info_temp_t *infor)
{
	char		sql[128];
	int			rv;
	void		*data=(void *)infor;
	char		*zErrMsg=0;
  
	if(NULL==infor)
	{
		log_error("The parameter is NULL!");
		return -1;
	}
	memset(sql,0,sizeof(sql));
	snprintf(sql,sizeof(sql),"SELECT * FROM TEMP_INFO LIMIT 0,1;");

	rv = sqlite3_exec(db, sql, callback_select,data, &zErrMsg);
    if( rv != SQLITE_OK )
	{
    	log_error("SELECT SQL error: %s\n", zErrMsg);
     	sql_term(db);
		return -1;
    }
	log_info("select is successfully!\n");
    return 1;
}
/* -----------------------------插入一条数据----------------------------------- */

int sql_insert_one_data(info_temp_t *infor)
{
	int			 rv;
	char		**dbresult;
	int			row=0;
	int			column=0;
	int			maxid=0;
	char		 sql[128];
	int			 id=0;
	char		 *zErrMsg=0;
  
	if(NULL==infor)
	{
		log_error("the parameter is NULL!");
		return -1;
	}
	maxid = sql_maxid()+1;

	memset(sql,0,sizeof(sql));
	snprintf(sql,sizeof(sql),"INSERT INTO TEMP_INFO (ID,PROD_ID,TIME,TEMP)"\
			"VALUES (%d,'%s', '%s','%s');",maxid,infor->prod_id,
			infor->ttime,infor->temp);
	rv = sqlite3_get_table(db, sql, &dbresult,&row,&column, &zErrMsg);
    if( rv != SQLITE_OK )
	{
    	log_error("INSERT SQL error: %s\n", zErrMsg);
		sql_term(db);
		return -1;
    }
	else
	{
    	log_info("insert operation done successfully\n");
    }
    return 1;
}

/* -------------------------------删掉表中第一条数据--------------------------------- */

int sql_delete_one_data()
{
	int			rv;
	char		**dbresult;
	int			row=0;
	int			column=0;
	int			minid=0;
	char		*zErrMsg=0;
	char		sql[64];
  
	minid=sql_minid();

	memset(sql,0,sizeof(sql));
	snprintf(sql,sizeof(sql),"DELETE FROM TEMP_INFO WHERE ID=%d;",minid);
	rv =sqlite3_get_table(db,sql,&dbresult,&row,&column, &zErrMsg);
    if( rv != SQLITE_OK )
	{
    	log_error("DELETE SQL error: %s\n", zErrMsg);
     	sql_term(db);
		return -1;
    }
	else
	{
    	log_info("delete operation done successfully\n");
    }
    return 1;
}
/*------------------------------关闭数据库----------------------------------*/
void sql_term()
{
	sqlite3_close(db);
}
